package com.hp.attendance.dao;

import com.hp.attendance.entity.*;
import com.hp.attendance.util.BaseDAO;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

public class EmployeeDao extends BaseDAO {

    /**
     * 根据员工编号和密码查询员工对象
     * @param employeeNumber
     * @param password
     * @return
     */
    public Employee getEmployee(String employeeNumber ,String password) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql="select * from employee where employee_number =? and password=?";
        Object[] obj = {employeeNumber,password};
        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }

    //分页查询
    public List<Employee> getLimitList(int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from employee limit ?,?";
        Object[] obj ={(pageNo-1)*pageSize,pageSize};
        List<Employee> employees = queryList(sql, obj, new Employee());
        return employees;
    }

    //查询总记录条数
    public long getTotal() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from employee";
        int count = queryCount(sql, new Object[0]);
        return count;
    }

    /**
     * 根据emp_id删除已分配的菜单
     * @param empId
     */
    public void delEmpRole(int empId) throws SQLException, IOException, ClassNotFoundException {
        String sql = "delete from emp_role where emp_id=?";
        Object[] obj={empId};
        addOrUpdOrDel(sql,obj);
    }
    /**
     * 执行菜单分配
     * @param lister
     */
    public void addEmpRole(List<EmpRole> lister) throws SQLException, IOException, ClassNotFoundException {
        StringBuilder sql = new StringBuilder("insert into emp_role values");
        for (int i = 0; i < lister.size(); i++) {
            sql.append("(?,?),");
        }
        String sqlExe = sql.substring(0, sql.length() - 1);
        //设置参数
        Object[] obj = new Object[lister.size() * 2];
        for (int i = 0; i < lister.size(); i++) {
            EmpRole empRole = lister.get(i);
            obj[2*i]=empRole.getEmp_id();
            obj[2*i+1]=empRole.getR_id();
        }
        addOrUpdOrDel(sqlExe,obj);
    }

    public Employee queryEmployee(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql="select * from employee where id=?";
        Object[] obj = {id};

        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }



    public void oneselfUpdate(Employee employee) throws SQLException, IOException, ClassNotFoundException {
        String sql="update employee set name=?,gender=?,birthday=?,telephone=?,email=?,address=?,education=?,password=?,notes=? where id=?" ;
        Object[] obj = {employee.getName(),employee.getGender(),employee.getBirthday(),employee.getTelephone(),employee.getEmail(),employee.getAddress(),employee.getEducation(),employee.getPassword(),employee.getNotes(),employee.getId()};
        addOrUpdOrDel(sql,obj);
    }

    public List<Employee> getList() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from employee";
        List<Employee> list = queryList(sql, new Object[0], new Employee());
        return list;
    }

    public Employee getEmployeeByEmpNum(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql="select * from employee where employee_number=?";
        Object[] obj = {id};
        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }


    public void empUpdate(Employee employee) throws SQLException, IOException, ClassNotFoundException {
        String sql="update employee set name=?,gender=?,birthday=?,telephone=?,email=?,address=?,education=?,department_number=?,position_number=?,password=?,notes=? where id=?" ;
        Object[] obj = {employee.getName(),employee.getGender(),employee.getBirthday(),employee.getTelephone(),employee.getEmail(),employee.getAddress(),employee.getEducation(),employee.getDepartment_number(),employee.getPosition_number(),employee.getPassword(),employee.getNotes(),employee.getId()};
        addOrUpdOrDel(sql,obj);
    }

    public void empDelete(int id) throws SQLException, IOException, ClassNotFoundException {
        String sql="delete from employee where id=?";
        Object[] obj ={id};
        addOrUpdOrDel(sql,obj);
    }

    public void empAdd(Employee employee) throws SQLException, IOException, ClassNotFoundException {
        //employee_number,name, password, gender,
        // birthday1, telephone, email, address,
        // education, department_number, position_number, notes
        String sql="insert into employee values(null,?,?,?,?,?,?,?,null,?,?,?,?,?,?)";
        Object[] obj={employee.getEmployee_number(),employee.getName(),employee.getGender(),
        employee.getBirthday(),employee.getTelephone(),employee.getEmail(),employee.getAddress(),
        employee.getEducation(),employee.getDepartment_number(), employee.getPosition_number(),employee.getIn_time(),employee.getPassword(),employee.getNotes()};
        addOrUpdOrDel(sql,obj);
    }
}
